#!/usr/bin/env python
################################################################################
## NAME: quadrant - sysbench load test framework
## DATE: 2011-02-04
## ID: $Id: quadrant.py 26 2011-05-27 00:41:37Z themattreid $
## AUTHOR: Matt Reid
## WEBSITE: http://kontrollsoft.com
## EMAIL: mreid@kontrollsoft.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
################################################################################
from __future__ import division
from ConfigParser import ConfigParser
import commands
import sys
import os
import re
import csv
import time
import string
import datetime
import logging
import MySQLdb
import getopt
import helix
from optparse import OptionParser


def logger(string,level):
    '''
    @PURPOSE: output information to the console in different formats
    @RETURNS: nothing
    @USAGE:
    console("write this to console","d") #outputs string without any change, sends to logger(if enabled), level: DEBUG
    console("write this to console","w") #outputs string without any change, sends to logger(if enabled), level: WARN
    console("write this to console","i") #outputs string without any change, sends to logger(if enabled), level: INFO
    console("write this to console","e") #outputs string without any change, sends to logger(if enabled), level: ERROR
    logger("write this to console","c") #outputs string without any change, sends to logger(if enabled), level: CRIT    
    '''    
    if level == "d":
        log.debug(string)
    if level == "w": 
        log.warn(string)
    if level == "i": 
        log.info(string)
    if level == "e":
        log.error(string)
    if level == "c": 
        log.critical(string)

def parse_options():
    usage = "usage: "
    parser = OptionParser(usage=usage)

    ## Standard Base Options - sysbench driven
    parser.add_option("--sysbench-binary", dest="sysbench_binary", default="sysbench", help="Path location of the sysbench binary. Default is simply 'sysbench' which assumes it is available in your path. Recommend it to be set specifically if you are running automated tests or unattended runs.")
    parser.add_option("--num-threads", dest="num_threads", default="1", help="The total number of worker threads to create")
    parser.add_option("--max-requests", dest="max_requests", default="10000", help="Limit for total number of requests. 0 means unlimited")
    parser.add_option("--max-time", dest="max_time", default="60", help="Limit for total execution time in seconds. 0 means unlimited")
    parser.add_option("--thread-stack-size", dest="thread_stack_size", default="32K", help="Size of stack for each thread")
    parser.add_option("--init-rng", dest="init_rng", default="off", help="Specifies if random numbers generator should be initialized from timer before the test start")
    parser.add_option("--test", dest="test", default="oltp-standard", help="Available types: oltp-standard, oltp-standard-qcache, oltp-sql, olap, cpu, memory, threads, mutex, fileio")
    parser.add_option("--debug", dest="debug", action="store_true", help="Print debug info to STDOUT (same as --verbosity=5) and write to file specified in --debug-log")
    parser.add_option("--verbosity", dest="verbosity", default="4", help="Verbosity level (1 - only critical messages, 5 - debug)")
    parser.add_option("--percentile", dest="percentile", default="95", help="SysBench measures execution times for all processed requests to display statistical information like minimal, average and maximum execution time. For most benchmarks it is also useful to know a request execution time value matching some percentile (e.g. 95% percentile means we should drop 5% of the most long requests and choose the maximal value from the remaining ones). This option allows to specify a percentile rank of query execution times to count")    
    parser.add_option("--validate", dest="validate", default="off", help="Perform validation of test results where possible")

    ## OLTP test options - sysbench driven
    parser.add_option("--oltp-test-mode", dest="oltp_test_mode", default="complex", help="Execution mode (see above). Possible values: simpe (simple), complex (advanced transactional) and nontrx (non-transactional)")
    parser.add_option("--oltp-read-only", dest="oltp_read_only", default="off", help="Read-only mode. No UPDATE, DELETE or INSERT queries will be performed.")
    parser.add_option("--oltp-skip-trx", dest="oltp_skip_trx", action="store_true", help="Omit BEGIN/COMMIT statements, i.e. run the same queries as the test would normally run but without using transactions.")
    parser.add_option("--oltp-reconnect-mode", dest="oltp_reconnect_mode", default="session", help="NOT IMPLEMENTED due to 0.4.10 limitations (default version for RHEL5). Reconnect mode. Possible values: [session: Don't reconnect (i.e. each thread disconnects only at the end of the test)], [query: Reconnect after each SQL query], [transaction: Reconnect after each transaction (if transactions are used in the selected DB test)],[random: One of the above modes is randomly chosen for each transaction]")
    parser.add_option("--oltp-range-size", dest="oltp_range_size", default="100", help="Range size for range queries")
    parser.add_option("--oltp-point-selects", dest="oltp_point_selects", default="10", help="Number of point select queries in a single transaction")
    parser.add_option("--oltp-simple-ranges", dest="oltp_simple_ranges", default="1", help="Number of simple range queries in a single transaction")
    parser.add_option("--oltp-sum-ranges", dest="oltp_sum_ranges", default="1", help="Number of SUM range queries in a single transaction")
    parser.add_option("--oltp-order-ranges", dest="oltp_order_ranges", default="1", help="Number of ORDER range queries in a single transaction")
    parser.add_option("--oltp-distinct-ranges", dest="oltp_distinct_ranges", default="1", help="Number of DISTINCT range queries in a single transaction")
    parser.add_option("--oltp-index-updates", dest="oltp_index_updates", default="1", help="Number of index UPDATE queries in a single transaction")
    parser.add_option("--oltp-non-index-updates", dest="oltp_non_index_updates", default="1", help="Number of non-index UPDATE queries in a single transaction")
    parser.add_option("--oltp-nontrx-mode", dest="oltp_nontrx_mode", default="select", help="Type of queries for non-transactional execution mode (see above). Possible values: select, update_key, update_nokey, insert, delete.")
    parser.add_option("--oltp-connect-delay", dest="oltp_connect_delay", default="10000", help="Time in microseconds to sleep after each connection to database")
    parser.add_option("--oltp-user-delay-min", dest="oltp_user_delay_min", default="0", help="Minimum time in microseconds to sleep after each request")
    parser.add_option("--oltp-user-delay-max", dest="oltp_user_delay_max", default="0", help="Maximum time in microseconds to sleep after each request")
    parser.add_option("--oltp-table-name", dest="oltp_table_name", default="sbtest", help="Name of the test table")
    parser.add_option("--oltp-table-size", dest="oltp_table_size", default="10000", help="Number of rows in the test table")
    parser.add_option("--oltp-dist-type", dest="oltp_dist_type", default="special", help="Distribution of random numbers. Possible values: uniform (uniform distribution), gauss (gaussian distribution) and special. With special distribution a specified percent of numbers is generated in a specified percent of cases (see options below).")
    parser.add_option("--oltp-dist-pct", dest="oltp_dist_pct", default="1", help="Percentage of values to be treated as 'special' (for special distribution)")
    parser.add_option("--oltp-dist-res", dest="oltp_dist_res", default="75", help="Percentage of cases when 'special' values are generated (for special distribution)")

    ## MySQL connection options - sysbench driven
    parser.add_option("--db-ps-mode", dest="db_ps_mode", default="auto", help="If the database driver supports Prepared Statements API, SysBench will use server-side prepared statements for all queries where possible. Otherwise, client-side (or emulated) prepared statements will be used. This option allows to force using emulation even when PS API is available. Possible values: disable, auto.")
    parser.add_option("--mysql-host", dest="mysql_host", default="localhost", help="MySQL server host. Starting from version 0.4.5 you may specify a list of hosts separated by commas. In this case SysBench will distribute connections between specified MySQL hosts on a round-robin basis. Note that all connection ports and passwords must be the same on all hosts. Also, databases and tables must be prepared explicitely on each host before executing the benchmark.")
    parser.add_option("--mysql-port", dest="mysql_port", default="3306", help="MySQL server port (in case TCP/IP connection should be used)")
    parser.add_option("--mysql-socket", dest="mysql_socket", default="", help="Unix socket file to communicate with the MySQL server")
    parser.add_option("--mysql-user", dest="mysql_user", default="sbtest", help="MySQL user")
    parser.add_option("--mysql-password", dest="mysql_password", default="sbtest", help="MySQL password")
    parser.add_option("--mysql-db", dest="mysql_db", default="sbtest", help="MySQL database name. Note SysBench will not automatically create this database. You should create it manually and grant the appropriate privileges to a user which will be used to access the test table.")
    parser.add_option("--mysql-table-engine", dest="mysql_table_engine", default="innodb", help="Type of the test table. Possible values: myisam, innodb, heap, ndbcluster, bdb, maria, falcon, pbxt")
    parser.add_option("--mysql-ssl", dest="mysql_ssl", default="no", help="Use SSL connections.")
    parser.add_option("--myisam-max-rows", dest="myisam_max_rows", default="1000000", help="MAX_ROWS option for MyISAM tables (required for big tables)")
    parser.add_option("--mysql-create-options", dest="mysql_create_options", default="", help="Additional options passed to CREATE TABLE.")

    ## Framework options - not specific to sysbench
    parser.add_option("--snmp-port", dest="snmp_port", default="161", help="SNMP host port. (default: 161)")
    parser.add_option("--snmp-host", dest="snmp_host", default="127.0.0.1", help="SNMP host address. (default: <address of mysql>)")
    parser.add_option("--snmp-rocommunity", dest="snmp_rocommunity", default="public", help="SNMP read only community value (default: public)")
    parser.add_option("--snmp-version", dest="snmp_version", default="1", help="SNMP version. [1,2,3] (default: 1)")
    parser.add_option("--snmp-disable", dest="snmp_disable", action="store_true", help="Set to disable SNMP metric polling. This will remove access to the following metrics: osLoadMinOne, osLoadMinFive, osLoadMinFifteen, cpuPercUser, cpuPercSys, cpuPercIdle, swapBytesTotal, swapBytesAvail, memBytesTotalAvail, memBytesTotalUsed, memBytesTotalFree, memBytesTotalShared, memBytesTotalBuffered, memBytesTotalCached.")
    parser.add_option("--mysql-iteration-restart", dest="mysql_iteration_restart", help="Command to use for enabling a MySQL service restart in between iterations. You must encapsulate the command in quotes for this to work. This is useful if you want all buffers and caches to be cleared in between tests. Note: this does tear down your DB connection(s) and requires all connections to be rebuilt on each iteration, thus adding that amount of system overhead. Default: Disabled")
    parser.add_option("--mysql-iteration-dynamic-variable", dest="mysql_iteration_dynamic_variable", default="", help="Dynamic variable to change between iterations. Requires --mysql-iteration-dynamic-variable-min and --mysql-iteration-dynamic-variable-inc to be set.")
    parser.add_option("--mysql-iteration-dynamic-variable-min", dest="mysql_iteration_dynamic_variable_min", default="", help="Minimum value for the dynamic variable value (start of iterations) as set by --mysql-iteration-dynamic-variable.")
    parser.add_option("--mysql-iteration-dynamic-variable-inc", dest="mysql_iteration_dynamic_variable_inc", default="", help="Incremental value for the dynamic variable value (start of iterations) as set by --mysql-iteration-dynamic-variable.")
    parser.add_option("--mysql-iteration-global-status-watch", dest="mysql_iteration_global_status_watch", default="", help="comma separates list of global status variables to monitor during tests. There associated values will be output in report during each iteration of testing. Unlimited number of values allowed within available global status variables.")
    parser.add_option("--mysql-iteration-sql-execute", dest="mysql_iteration_sql_execute", default="", help="SQL command to execute between interations. Can be a semi-colon separated list of multiple queries if multiple queries require execution.")
    parser.add_option("--mysql-iteration-query-cache-flush", dest="mysql_iteration_query_cache_flush", action="store_true", help="If enabled this will flush the query cache between each test iteration.")
    parser.add_option("--iteration-system-command-exec", dest="iteration_system_command_exec", default="", help="System command to run between iterations. Multiple commands can be separated by standard command line operators. Note: if you are running tests on a remote DB host you NEED to set any destination-host specific commands to use SSH with keys, otherwise the command(s) will be run on the host running the Quadrant scripts.")
    parser.add_option("--iteration-sleep", dest="iteration_sleep", default="0", help="Value in seconds to sleep in between iterations. Default (0)")

    ## Output options
    parser.add_option("--output-file-format", dest="output_file_format", default="csv", help="Format style of test results. Options: [csv, xml, json]")    
    parser.add_option("--output-oltp-limit-results", dest="output_oltp_limit_results", default="", help="OLTP test outputs the following standard results, of which you can limit with this option using a comma separate list of the output you want: [numThreads,dataSetSize, Qread, Qwrite, Qother, Qtotal, operTx, operTxPerSec, operDeadLocks, operDeadLocksPerSec, operRequests, operRequestsPerSec, operOther, operOtherPerSec, totalExecTime, totalExecEvents, execExecSum, execPerRequestMin, execPerRequestMax, execPerRequestAvg, execPerRequest95perc, threadsEventAvg, threadsEventStddev, threadsExecTimeAvg, threadsExecTimeStddev, osLoadMinOne, osLoadMinFive, osLoadMinFifteen, cpuPercUser, cpuPercSys, cpuPercIdle, swapBytesTotal, swapBytesAvail, memBytesTotalAvail, memBytesTotalUsed, memBytesTotalFree, memBytesTotalShared, memBytesTotalBuffered, memBytesTotalCached]. For definitions of these values please see the documentation.")
    parser.add_option("--output-graphcode-enable", action="store_true", dest="output_graphcode_enable", default="no", help="When set to 'yes', graph code is created and generated with data points for viewing in a web browser. If not enabled you will ONLY get data point output in the selected format from --output-file-format.")
    parser.add_option("--output-graphcode-framework", dest="output_graphcode_framework", default="dygraphs", help="The graphing framework to use for auto-graph generation. DyGraphs is a lightweight implementation that reads CSV files directly, which makes it very easy to modify data sources if you don't want to edit HTML, Sparklines generates compact small graphs, Graphite is a realtime framework that offers graphs via web-browser while the load tests are running (Requires Graphite to be installed and ready for data input and requires secondary command line options to be set:  Requires --output-graphcode-enable=yes. Options: dygraphs [currently the only option implemented], graphite, sparklines")
    parser.add_option("--output-directory", dest="output_directory", default="output", help="Directory used to write reports to. Default: output")
    parser.add_option("--debug-log", dest="debug_log", default="quadrant.debug.log", help="File to log debug info to. Default: quadrant.debug.log")
    parser.add_option("--print-sql", dest="print_sql", action="store_true", help="Prints the SQL commands for sysbench schema creation.")
    parser.add_option("--loops", dest="loops", default="1", help="Number of loops to run for tests. Default: 1") 
    parser.add_option("--conf-file", dest="conf_file", default="", help="Configuration file to use. Note: if this is selected all command line arguments are ignored. Default: none")
    
    #parser.add_option("", dest="", default="", help="")
    return parser.parse_args()

def getconf():    
    try:
        config = ConfigParser()
        config.read([options.conf_file])
        c = 'quadrant configuration'
        sysbench_binary = str(config.get(c,"sysbench-binary"))
        num_threads = int(config.get(c,"num-threads"))
        max_requests = int(config.get(c,"max-requests"))
        max_time = int(config.get(c,"max-time"))
        thread_stack_size = str(config.get(c,"thread-stack-size"))
        init_rng = str(config.get(c,"init-rng"))
        test = str(config.get(c,"test"))
        debug = str(config.get(c,"debug"))
        verbosity = str(config.get(c,"verbosity"))
        percentile = int(config.get(c,"percentile"))
        validate = str(config.get(c,"validate"))
        oltp_test_mode = str(config.get(c,"oltp-test-mode"))
        oltp_read_only = str(config.get(c,"oltp-read-only"))
        oltp_skip_trx = str(config.get(c,"oltp-skip-trx"))
        oltp_reconnect_mode = str(config.get(c,"oltp-reconnect-mode"))
        oltp_range_size = int(config.get(c,"oltp-range-size"))
        oltp_point_selects = int(config.get(c,"oltp-point-selects"))
        oltp_simple_ranges = int(config.get(c,"oltp-simple-ranges"))
        oltp_sum_ranges = int(config.get(c,"oltp-sum-ranges"))
        oltp_order_ranges = int(config.get(c,"oltp-order-ranges"))
        oltp_distinct_ranges = int(config.get(c,"oltp-distinct-ranges"))
        oltp_index_updates = int(config.get(c,"oltp-index-updates"))
        oltp_non_index_updates = int(config.get(c,"oltp-non-index-updates"))
        oltp_nontrx_mode = str(config.get(c,"oltp-nontrx-mode"))
        oltp_connect_delay = str(config.get(c,"oltp-connect-delay"))
        oltp_user_delay_min = int(config.get(c,"oltp-user-delay-min"))
        oltp_user_delay_max = int(config.get(c,"oltp-user-delay-max"))
        oltp_table_name = str(config.get(c,"oltp-table-name"))
        oltp_table_size = int(config.get(c,"oltp-table-size"))
        oltp_dist_type = str(config.get(c,"oltp-dist-type"))
        oltp_dist_pct = int(config.get(c,"oltp-dist-pct"))
        oltp_dist_res = int(config.get(c,"oltp-dist-res"))
        db_ps_mode = str(config.get(c,"db-ps-mode"))
        mysql_host = str(config.get(c,"mysql-host"))
        mysql_port = int(config.get(c,"mysql-port"))
        mysql_socket = str(config.get(c,"mysql-socket"))
        mysql_user = str(config.get(c,"mysql-user"))
        mysql_password = str(config.get(c,"mysql-password"))
        mysql_db = str(config.get(c,"mysql-db"))
        mysql_table_engine = str(config.get(c,"mysql-table-engine"))
        mysql_ssl = str(config.get(c,"mysql-ssl"))
        myisam_max_rows = int(config.get(c,"myisam-max-rows"))
        mysql_create_options = str(config.get(c,"mysql-create-options"))
        snmp_port = int(config.get(c,"snmp-port"))
        snmp_host = str(config.get(c,"snmp-host"))
        snmp_rocommunity = str(config.get(c,"snmp-rocommunity"))
        snmp_version = str(config.get(c,"snmp-version"))
        snmp_disable = config.get(c,"snmp-disable")
        mysql_iteration_restart = config.get(c,"mysql-iteration-restart")
        mysql_iteration_dynamic_variable = str(config.get(c,"mysql_iteration_dynamic_variable"))
        mysql_iteration_dynamic_variable_min = int(config.get(c,"mysql_iteration_dynamic_variable_min"))
        mysql_iteration_dynamic_variable_inc = int(config.get(c,"mysql_iteration_dynamic_variable_inc"))
        mysql_iteration_global_status_watch = str(config.get(c,"mysql-iteration-global-status-watch"))
        mysql_iteration_sql_execute = str(config.get(c,"mysql-iteration-sql-execute"))
        mysql_iteration_query_cache_flush = str(config.get(c,"mysql-iteration-query-cache-flush"))
        iteration_system_command_exec = config.get(c,"iteration-system-command-exec")
        iteration_sleep = config.get(c,"iteration-sleep")
        output_file_format = str(config.get(c,"output-file-format"))
        output_oltp_limit_results = str(config.get(c,"output-oltp-limit-results"))
        output_graphcode_enable = str(config.get(c,"output-graphcode-enable"))
        output_graphcode_framework = str(config.get(c,"output-graphcode-framework"))
        output_directory = str(config.get(c,"output-directory"))
        debug_log = str(config.get(c,"debug-log"))
        print_sql = str(config.get(c,"print-sql"))
        loops = int(config.get(c,"loops"))
        return sysbench_binary,num_threads,max_requests,max_time,thread_stack_size,init_rng,test,debug,verbosity,percentile,validate,oltp_test_mode,oltp_read_only,oltp_skip_trx,oltp_reconnect_mode,oltp_range_size,oltp_point_selects,oltp_simple_ranges,oltp_sum_ranges,oltp_order_ranges,oltp_distinct_ranges,oltp_index_updates,oltp_non_index_updates,oltp_nontrx_mode,oltp_connect_delay,oltp_user_delay_min,oltp_user_delay_max,oltp_table_name,oltp_table_size,oltp_dist_type,oltp_dist_pct,oltp_dist_res,db_ps_mode,mysql_host,mysql_port,mysql_socket,mysql_user,mysql_password,mysql_db,mysql_table_engine,mysql_ssl,myisam_max_rows,mysql_create_options,snmp_port,snmp_host,snmp_rocommunity,snmp_version,snmp_disable,mysql_iteration_restart,mysql_iteration_dynamic_variable,mysql_iteration_dynamic_variable_min,mysql_iteration_dynamic_variable_inc,mysql_iteration_global_status_watch,mysql_iteration_sql_execute,mysql_iteration_query_cache_flush,iteration_system_command_exec,iteration_sleep,output_file_format,output_oltp_limit_results,output_graphcode_enable,output_graphcode_framework,output_directory,debug_log,print_sql,loops
    except:
        logger("Failed to parse config file options.","i")
        sys.exit(1)


def header():
    #print code_version
    print '''QUADRANT - MySQL Load Test Framework
site: http://code.google.com/p/quadrant-framework
author: Matt Reid
license: BSD
'''

def printsql():
    print '''
CREATE DATABASE sbtest;
USE sbtest;
CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
GRANT SUPER ON *.* TO 'sbtest'@'localhost' IDENTIFIED BY 'sbtest';
GRANT ALL ON sbtest.* TO 'sbtest'@'localhost' IDENTIFIED BY 'sbtest';

'''
    sys.exit(0)

def get_snmp(snmp_request):
    '''once I find some code for pysnmp that actually doesn't suck balls...'''
    '''if not supported then use the following command method'''
    logger("SNMP check: %s"%(snmp_request),'i')
    timing,retcode,snmpwalk_binary = helix.sysexec("which snmpwalk")
    if snmpwalk_binary != False:
        logger("running snmpwalk command: %s -O qvU -v %s -c %s %s:%s %s"%(snmpwalk_binary,
                                                                           snmp_version,
                                                                           snmp_rocommunity,
                                                                           snmp_host,
                                                                           snmp_port,
                                                                           snmp_request),'d')
        
        timing,retcode,value = helix.sysexec("%s -O qvU -v %s -c %s %s:%s %s"%(snmpwalk_binary,
                                                                        snmp_version,
                                                                        snmp_rocommunity,
                                                                        snmp_host,
                                                                        snmp_port,
                                                                        snmp_request))
        logger("SNMP command timing: %s"%(timing),'d')
        logger("SNMP command retcode: %s"%(retcode),'d')
        logger("SNMP command value: %s"%(value),'d')
        if retcode != 0:
            logger("SNMP statistics collection failed: %s - Skipping this metric and setting value to False."%(snmp_request),'c')
            return False
    else:
        logger("Cannot find snmpwalk binary. please install and put the binary location into your $PATH",'c')
        return False    
    return value

def check_query_cache(conn):
    '''check to see if Query cache is enabled'''
    logger("def: check_query_cache","d")
    qcache = db.row(conn,"show global variables like 'have_query_cache'")    
    if qcache['Value'] == "ON" or qcache['Value'] == "YES":
        type = db.row(conn,"show global variables like 'query_cache_type'")
        if type['Value'] == "OFF" or qcache['Value'] == "0":
            return False
        else:
            return True
    else:
        return False

def sanitize(array):
    res = ""
    list = []
    for str in array:
        rep = [ ",","\r","\n","(",")","$","%","^","&","*","+","!","=","~","`","{","}","[","]","|","?","<",">","@","'",";","s"]
        for r in rep:
            str = str.replace(r,'')
        str = str.strip()
        list.append(str)
    for s in list:
        res+=s
    return res
    
def parse_report(report):
    logger("def: parse_report","d")
    if report == "":
        logger("Sysbench report does not exist. Tests failed.",'c')
        sys.exit(1)
    logger("Iteration sysbench report: %s"%(report),'d')

    global Qread,Qwrite,Qother,Qtotal,operTx,operTxPerSec,operDeadLocks,operDeadLocksPerSec,operRequests,operRequestsPerSec,operOther,operOtherPerSec,totalTime,totalExecTime,totalExecEvents,execExecSum,execPerRequestMin,execPerRequestMax,execPerRequestAvg,execPerRequest95perc,threadsEventAvg,threadsEventStddev,threadsExecTimeAvg,threadsExecTimeStddev
    
    for line in report.split('\n'):
        for item in ["read:",
                     "write:",
                     "other:",
                     "total:",
                     "transactions:",
                     "deadlocks:",
                     "read/write requests:",
                     "other operations:",
                     "total time:",
                     "total number",
                     "total time taken",
                     "min:",
                     "avg:",
                     "max:",
                     "approx.",
                     "events",
                     "execution time"]:

            match = re.search(item, line)
            if match is not None:
                l = line.split()
                logger("Parsing: %s"%(l),'d')
                if l[0] == "read:":
                    Qread = l[1]
                if l[0] == "write:":
                    Qwrite = l[1]
                if l[0] == "other:":
                    Qother = l[1]
                if l[0] == "total:":
                    Qtotal = l[1]
                if l[0] == "transactions:":
                    operTx = str(l[1])
                    operTxPerSec = sanitize(l[2])
                if l[0] == "deadlocks:":  
                    operDeadLocks = str(l[1])
                    operDeadLocksPerSec = sanitize(l[2])                    
                if l[0]+" "+l[1] == "read/write requests:":
                    operRequests = l[2]
                    operRequestsPerSec = sanitize(l[3])
                if l[0]+" "+l[1] == "other operations:":
                    operOther = l[2]
                    operOtherPerSec = sanitize(l[3])
                if l[0]+" "+l[1] == "total time:":
                    totalTime = sanitize(l[2])
                if l[0]+" "+l[1] == "total number":
                    totalExecEvents = sanitize(l[4])
                if l[0]+" "+l[1] == "total time":
                    totalExecTime = sanitize(l[6])
                if l[0] == "min:":
                    execPerRequestMin = l[1]
                if l[0] == "avg:":
                    execPerRequestAvg = l[1]
                if l[0] == "max:":
                    execPerRequestMax = l[1]
                if l[0] == "approx.":
                    execPerRequest95perc = l[3]
                if l[0]+" "+l[1] == "events (avg/stddev):":                    
                    v = l[2].split("/")
                    threadsEventAvg = v[0]
                    threadsEventStddev = v[1]
                if l[0]+" "+l[1] == "execution time":
                    v = l[3].split("/")
                    threadsExecTimeAvg = v[0]
                    threadsExecTimeStddev = v[1]

def dir_test(f):
    logger("Testing directory existence: %s"%(f),'i')
    if not os.path.exists(f):
        try:
            os.makedirs(f)
        except OSError:
            logger("[create-failed]",'c')
            sys.exit(1)

def iteration_global_status_watch():
    dict = {}
    items = mysql_iteration_global_status_watch.split(",")
    for i in items:
        var = db.row(conn,"show global status like '%s'"%(i))
        try:
            dict[i] = var['Value']
        except:
            continue
    return dict

def iteration_global_var_watch():
    dict = {}    
    var = db.row(conn,"show global variables like '%s'"%(mysql_iteration_dynamic_variable))
    try:
        dict[mysql_iteration_dynamic_variable] = var['Value']
    except:
        return
    return dict

def csv_initial(mysql_iteration_dynamic_variable):
    rightnow = datetime.datetime.now() 
    dict = {"numThreads":num_threads,
            "dataSetSize":dataSetSize,
            "Qread":Qread,
            "Qwrite":Qwrite,
            "Qother":Qother,
            "Qtotal":Qtotal,
            "operTx":operTx,
            "operTxPerSec":operTxPerSec,
            "operDeadLocks":operDeadLocks,
            "operDeadLocksPerSec":operDeadLocksPerSec,
            "operRequests":operRequests,
            "operRequestsPerSec":operRequestsPerSec,
            "operOther":operOther,
            "operOtherPerSec":operOtherPerSec,
            "totalTime":totalTime,
            "totalExecTime":totalExecTime,
            "totalExecEvents":totalExecEvents,
            "execExecSum":execExecSum,
            "execPerRequestMin":execPerRequestMin,
            "execPerRequestMax":execPerRequestMax,
            "execPerRequestAvg":execPerRequestAvg,
            "execPerRequest95perc":execPerRequest95perc,
            "threadsEventAvg":threadsEventAvg,
            "threadsEventStddev":threadsEventStddev,
            "threadsExecTimeAvg":threadsExecTimeAvg,
            "threadsExecTimeStddev":threadsExecTimeStddev,
            "osLoadMinOne":osLoadMinOne,
            "osLoadMinFive":osLoadMinFive,
            "osLoadMinFifteen":osLoadMinFifteen,
            "cpuPercUser":cpuPercUser,
            "cpuPercSys":cpuPercSys,
            "cpuPercIdle":cpuPercIdle,
            "swapBytesTotal":swapBytesTotal,
            "swapBytesAvail":swapBytesAvail,
            "memBytesTotalAvail":memBytesTotalAvail,
            "memBytesTotalUsed":memBytesTotalUsed,
            "memBytesTotalFree":memBytesTotalFree,
            "memBytesTotalShared":memBytesTotalShared,
            "memBytesTotalBuffered":memBytesTotalBuffered,
            "memBytesTotalCached":memBytesTotalCached}
    
    f = "%s/%s_%s.csv"%(output_directory,mysql_host,now)
    try:            
        file = open(f, 'a')
    except:
        logger("Failed to write report to file: %s."%(f),"c")
        sys.exit(1)
    
    if mysql_iteration_dynamic_variable != False:
        dict[mysql_iteration_dynamic_variable] = mysql_iteration_dynamic_variable
        output_oltp_limit_results+str(mysql_iteration_dynamic_variable)

    if mysql_iteration_global_status_watch:
        stats = iteration_global_status_watch()
        for k,v in stats.items():
            dict[k] = v

    if mysql_iteration_dynamic_variable:
        vars = iteration_global_var_watch()
        for k,v in vars.items():
            dict[k] = v

    file.write("Date,")
    d = sorted(dict.items())
    if output_oltp_limit_results:
        o = output_oltp_limit_results.split(",")
        logger("--output-oltp-limit-results set: %s"%(o),'d')
        for k in o:            
            logger("writing value: %s"%(k),'d')
            file.write("%s,"%(k))

    else:
        z = 0
        while z < 1:
            for k,v in d:
                file.write("%s,"%(k)) 
                z += 1

    file.write("\n")
    file.close
    return

def csv_loop(mysql_iteration_dynamic_variable,mysql_iteration_dynamic_variable_cur):    
    rightnow = datetime.datetime.now()
    dict = {"numThreads":num_threads,
            "dataSetSize":helix.human(dataSetSize),
            "Qread":Qread,
            "Qwrite":Qwrite,
            "Qother":Qother,
            "Qtotal":Qtotal,
            "operTx":operTx,
            "operTxPerSec":operTxPerSec,
            "operDeadLocks":operDeadLocks,
            "operDeadLocksPerSec":operDeadLocksPerSec,
            "operRequests":operRequests,
            "operRequestsPerSec":operRequestsPerSec,
            "operOther":operOther,
            "operOtherPerSec":operOtherPerSec,
            "totalTime":totalTime,
            "totalExecTime":totalExecTime,
            "totalExecEvents":totalExecEvents,
            "execExecSum":execExecSum,
            "execPerRequestMin":execPerRequestMin,
            "execPerRequestMax":execPerRequestMax,
            "execPerRequestAvg":execPerRequestAvg,
            "execPerRequest95perc":execPerRequest95perc,
            "threadsEventAvg":threadsEventAvg,
            "threadsEventStddev":threadsEventStddev,
            "threadsExecTimeAvg":threadsExecTimeAvg,
            "threadsExecTimeStddev":threadsExecTimeStddev,
            "osLoadMinOne":osLoadMinOne,
            "osLoadMinFive":osLoadMinFive,
            "osLoadMinFifteen":osLoadMinFifteen,
            "cpuPercUser":cpuPercUser,
            "cpuPercSys":cpuPercSys,
            "cpuPercIdle":cpuPercIdle,
            "swapBytesTotal":swapBytesTotal,
            "swapBytesAvail":swapBytesAvail,
            "memBytesTotalAvail":memBytesTotalAvail,
            "memBytesTotalUsed":memBytesTotalUsed,
            "memBytesTotalFree":memBytesTotalFree,
            "memBytesTotalShared":memBytesTotalShared,
            "memBytesTotalBuffered":memBytesTotalBuffered,
            "memBytesTotalCached":memBytesTotalCached}
    
    f = "%s/%s_%s.csv"%(output_directory,mysql_host,now)
    try:            
        file = open(f, 'a')
    except:
        logger("Failed to write report to file: %s."%(f),"c")
        sys.exit(1)
    
    if mysql_iteration_dynamic_variable != False:
        dict[mysql_iteration_dynamic_variable] = mysql_iteration_dynamic_variable_cur
        output_oltp_limit_results+str(mysql_iteration_dynamic_variable)

    if mysql_iteration_global_status_watch:
        stats = iteration_global_status_watch()
        for k,v in stats.items():
            dict[k] = v

    if mysql_iteration_dynamic_variable:
        vars = iteration_global_var_watch()
        for k,v in vars.items():
            dict[k] = v

    file.write("%s,"%(rightnow))
    d = sorted(dict.items())
    if output_oltp_limit_results:
        o = output_oltp_limit_results.split(",")
        logger("--output-oltp-limit-results set: %s"%(o),'d')
        for k in o:
            logger("writing value: %s"%(k),'d')
            file.write("%s,"%(dict[k]))

    else:
        for k,v in d:
            file.write("%s,"%(v))

    file.write("\n")
    file.close
    return

def get_snmp_stats():
    global osLoadMinOne,osLoadMinFive,osLoadMinFifteen,cpuPercUser,cpuPercSys,cpuPercIdle,swapBytesTotal,swapBytesAvail,memBytesTotalAvail,memBytesTotalUsed,memBytesTotalFree,memBytesTotalShared,memBytesTotalBuffered,memBytesTotalCached
    s = {"osLoadMinOne":".1.3.6.1.4.1.2021.10.1.3.1",
         "osLoadMinFive":".1.3.6.1.4.1.2021.10.1.3.2",
         "osLoadMinFifteen":".1.3.6.1.4.1.2021.10.1.3.3",
         "cpuPercUser":".1.3.6.1.4.1.2021.11.9.0",
         "cpuPercSys":".1.3.6.1.4.1.2021.11.10.0",
         "cpuPercIdle":".1.3.6.1.4.1.2021.11.11.0",
         "swapBytesTotal":".1.3.6.1.4.1.2021.4.3.0",
         "swapBytesAvail":".1.3.6.1.4.1.2021.4.4.0",
         "memBytesTotalAvail":".1.3.6.1.4.1.2021.4.5.0",
         "memBytesTotalUsed":".1.3.6.1.4.1.2021.4.6.0",
         "memBytesTotalFree":".1.3.6.1.4.1.2021.4.11.0",
         "memBytesTotalShared":".1.3.6.1.4.1.2021.4.13.0",
         "memBytesTotalBuffered":".1.3.6.1.4.1.2021.4.14.0",
         "memBytesTotalCached":".1.3.6.1.4.1.2021.4.15.0"}

    osLoadMinOne = get_snmp(s["osLoadMinOne"])
    osLoadMinFive = get_snmp(s["osLoadMinFive"])
    osLoadMinFifteen = get_snmp(s["osLoadMinFifteen"])
    cpuPercUser = get_snmp(s["cpuPercUser"])
    cpuPercSys = get_snmp(s["cpuPercSys"])
    cpuPercIdle = get_snmp(s["cpuPercIdle"])
    swapBytesTotal = get_snmp(s["swapBytesTotal"])
    swapBytesAvail = get_snmp(s["swapBytesAvail"])
    memBytesTotalAvail = get_snmp(s["memBytesTotalAvail"])
    memBytesTotalUsed = get_snmp(s["memBytesTotalUsed"])
    memBytesTotalFree = get_snmp(s["memBytesTotalFree"])
    memBytesTotalShared = get_snmp(s["memBytesTotalShared"])
    memBytesTotalBuffered = get_snmp(s["memBytesTotalBuffered"])
    memBytesTotalCached = get_snmp(s["memBytesTotalCached"])

def graph_handler():
    logger("def graph_handler: starting",'d')
    csv_orig = "%s/%s_%s.csv"%(output_directory,mysql_host,now)
    csv_dest = "%s_%s.csv"%(mysql_host,now)
    g = "%s/%s_%s.html"%(output_directory,mysql_host,now)
    logger("writing graph page to: %s"%(g),'d')

    if output_graphcode_framework == "dygraphs":
        js = "includes/js/dygraph-combined.js"
        if os.path.exists(js) != True and os.path.isfile(js) != True:
            logger("Failed to find JS file for graph generation: %s"%(js),'c')
            sys.exit(1)
        logger("copying JS file: %s to output dir: %s"%(js,output_directory),'d')
        timing,retcode,output = helix.sysexec("cp %s %s/"%(js,output_directory))
        if retcode != 0:
            logger("Failed to copy JS file for graph framework to output directory. Check the debug log for more info.",'c')
            sys.exit(1)
        logger("creating graphs for: dygraphs",'d')

        data = csv.reader(open(csv_orig))
        fields = data.next()
        date = ""
        list = []
        j = 0
        for row in data:
            i = zip(fields, row)
            for k,v in i:
                list.append(k)
                if k == "Date":
                    date = v
                try:
                    f = open("%s/%s.csv"%(output_directory,k),'a')
                except:
                    logger("Failed to write to CSV file: %s"%(f),'c')
                    sys.exit(1)
                if j == 0:
                    f.write("Date,%s\n"%(k)) #write row headers
                f.write("%s,%s\n"%(date,v))                
            j += 1

        '''write an individual graph file per report item'''
        for l in list:
            try:
                r = open("%s/%s.html"%(output_directory,l),'a')
            except:
                logger("Failed to write to HTML file: %s"%(r),'c')
                sys.exit(1)
            contents = '''<html>
<head>
<script type="text/javascript"
  src="dygraph-combined.js"></script>
</head>
<body>
<div id="graphdiv2"
  style="width:500px; height:300px;"></div>
<script type="text/javascript">
  g2 = new Dygraph(
    document.getElementById("graphdiv2"),
    "%s.csv", // path to CSV file
    {}    // options
  );
</script>
</body>
</html>
'''%(l)
            r.write(contents)
            r.close()
        
            '''write the main combined graph file'''
            try:
                file = open(g, 'a')
            except:
                logger("Failed to open file: %s."%(g),"c")
                sys.exit(1)
            contents = '''<html>
<head>
<script type="text/javascript"
  src="dygraph-combined.js"></script>
</head>
<body>
<div id="graphdiv2"
  style="width:500px; height:300px;"></div>
<script type="text/javascript">
  g2 = new Dygraph(
    document.getElementById("graphdiv2"),
    "%s", // path to CSV file
    {}    // options
  );
</script>
</body>
</html>
'''%(csv_dest)
            file.write(contents)
            file.close()
        return 
    elif output_graphcode_framework == "sparklines":
        logger("NOT IMPLEMENTED :: creating graphs for: sparklines",'i')
        return
    elif output_graphcode_framework == "graphite":
        logger("NOT IMPLEMENTED :: creating graphs for: graphite",'i')
        return
    else:
        logger("The --output-graphcode-framework is incorrectly set. Please see --help for available options.",'c')
        sys.exit(1)

def main():
    logger("def: main","d")
    global test, osMemTotal, osMemFree, conn, db
    qcache = False    
    sysbench = ""
    logger("Running test: %s"%(test),'i')
    
    if mysql_iteration_dynamic_variable:
        if not mysql_iteration_dynamic_variable_min:
            if mysql_iteration_dynamic_variable_min != 0:                
                logger("mysql_iteration_dynamic_variable IS set BUT mysql_iteration_dynamic_variable_min NOT set, cannot continue.",'c')
                sys.exit(1)
        if not mysql_iteration_dynamic_variable_inc:
            logger("mysql_iteration_dynamic_variable IS set BUT mysql_iteration_dynamic_variable_inc NOT set, cannot continue.",'c')
            sys.exit(1)
        mysql_iteration_dynamic_variable_cur = mysql_iteration_dynamic_variable_min
        logger("Setting baseline value of global variable, running SQL: set global %s=%i"%(mysql_iteration_dynamic_variable,mysql_iteration_dynamic_variable_min),'d')
        c = db.execute(conn,"set global %s=%i"%(mysql_iteration_dynamic_variable,mysql_iteration_dynamic_variable_min))
        if c == False:
            logger("Failed to alter mysql_iteration_dynamic_variable: %s. Canceling test."%(mysql_iteration_dynamic_variable),"i")
            sys.exit(1)

    loop = 1
    if snmp_disable == False:
        get_snmp_stats()
    if mysql_iteration_dynamic_variable:
        csv_initial(mysql_iteration_dynamic_variable)
    else:
        csv_initial(False)
    
    while loop <= loops:
        if mysql_iteration_restart:
            logger("Running --mysql-iteration-restart command: %s"%(mysql_iteration_restart),'i')
            timing,retcode,output = helix.sysexec(mysql_iteration_restart)
            if retcode != 0:
                logger("Timing: %s"%(timing),'d')
                logger("Retcode: %s"%(retcode),'d')
                logger("Output: %s"%(output),'d')
                logger("iteration --mysql-iteration-restart return value not zero. Not continuing with tests. RetValue: %i"%(retcode),'c')
                sys.exit(1)

            logger("Building database connection after MySQL process restart.",'i')
            db = helix.dbops()
            conn = db.connect(mysql_host,mysql_user,mysql_password,mysql_db)
            if conn == False:
                logger("DB connection failed.",'i')
                sys.exit(1)

        if test == "oltp-standard":
            test = "oltp"    
            qcache = check_query_cache(conn)        
            logger("Query Cache: %s"%(qcache),'i')
            if qcache == True:
                logger("Query cache enabled, attempting to disable for test. To use the Query Cache specify --test=oltp-standard-qcache",'i')
                logger("def main: set global query_cache_type=OFF","d")
                a = db.execute(conn,"set global query_cache_type=OFF")                

                logger("def main: set global query_cache_size=0","d")
                b = db.execute(conn,"set global query_cache_size=0")

                if a == False or b == False:
                    logger("Failed to disable query cache. Canceling test.","c")
                    sys.exit(1)

        if test == "oltp-standard-qcache":
            test = "oltp"
            qcache = check_query_cache(conn)
            logger("Query Cache: %s"%(qcache),'i')
            if qcache == False:
                logger("Query cache disabled, attempting to enable for test.","i")
                c = db.execute(conn,"set global query_cache_type='ON'")
                if c == False:
                    logger("Failed to enable query cache. Canceling test.","i")
                    sys.exit(1)

        if iteration_system_command_exec:
            logger("Running --iteration-system-command-exec: %s"%(iteration_system_command_exec),'i')
            timing,retcode,output = helix.sysexec(iteration_system_command_exec)
            logger("--iteration-system-command-exec return value: %i"%(retcode),'i')
            if retcode != 0:
                logger("iteration system command return value not zero. Not continuing with tests. RetValue: %i"%(retcode),'c')
                sys.exit(1)        

        if iteration_sleep:
            logger("Sleeping for %s seconds"%(iteration_sleep),'i')
            time.sleep(int(iteration_sleep))

        logger("Current iteration: %i/%i"%(loop,loops),"i")
        if mysql_iteration_query_cache_flush == True:
            d = db.execute(conn,"FLUSH QUERY CACHE;")
            if d == False:
                logger("Failed to flush query cache.","i")
                
        e = db.row(conn,"SELECT SUM(DATA_LENGTH) AS Value FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'sbtest' AND TABLE_NAME = 'sbtest';") 
        if e['Value'] == False:
            logger("Failed to get data set size. Test failed.",'c')
            sys.exit(1)
        global dataSetSize
        dataSetSize = e['Value']

        if mysql_iteration_dynamic_variable:
            logger("Running SQL: set global %s=%i"%(mysql_iteration_dynamic_variable,mysql_iteration_dynamic_variable_cur),'d')
            c = db.execute(conn,"set global %s=%i"%(mysql_iteration_dynamic_variable,mysql_iteration_dynamic_variable_cur))
            if c == False:
                logger("Failed to alter mysql_iteration_dynamic_variable: %s. Canceling test."%(mysql_iteration_dynamic_variable),"i")
                sys.exit(1)

        cmd = "%s --db-driver=mysql --num-threads=%s --max-requests=%s --max-time=%s --thread-stack-size=%s --init-rng=%s --test=%s --verbosity=3 --percentile=%s --validate=%s --oltp-test-mode=%s --oltp-read-only=%s --oltp-skip-trx=%s --oltp-range-size=%s --oltp-point-selects=%s --oltp-simple-ranges=%s --oltp-sum-ranges=%s --oltp-order-ranges=%s --oltp-distinct-ranges=%s --oltp-index-updates=%s --oltp-non-index-updates=%s --oltp-nontrx-mode=%s --oltp-connect-delay=%s --oltp-user-delay-min=%s --oltp-user-delay-max=%s --oltp-table-name=%s --oltp-table-size=%s --oltp-dist-type=%s --oltp-dist-pct=%s --oltp-dist-res=%s --db-ps-mode=%s --mysql-host=%s --mysql-port=%s --mysql-socket=%s --mysql-user=%s --mysql-password=%s --mysql-db=%s --mysql-table-engine=%s --mysql-ssl=%s --myisam-max-rows=%s --mysql-create-options=%s run"%(sysbench_binary,num_threads,max_requests,max_time,thread_stack_size,init_rng,test,percentile,validate,oltp_test_mode,oltp_read_only,oltp_skip_trx,oltp_range_size,oltp_point_selects,oltp_simple_ranges,oltp_sum_ranges,oltp_order_ranges,oltp_distinct_ranges,oltp_index_updates,oltp_non_index_updates,oltp_nontrx_mode,oltp_connect_delay,oltp_user_delay_min,oltp_user_delay_max,oltp_table_name,oltp_table_size,oltp_dist_type,oltp_dist_pct,oltp_dist_res,db_ps_mode,mysql_host,mysql_port,mysql_socket,mysql_user,mysql_password,mysql_db,mysql_table_engine,mysql_ssl,myisam_max_rows,mysql_create_options)
        logger("Running with: %s"%(cmd),"d")

        logger("Running sysbench test. Please be patient for approx(max(%s)) seconds."%(max_time),'i')
        res = helix.sysexec(cmd) #run sysbench test
        timing = res[0]
        code = res[1]
        out = parse_report(res[2]) #parse sysbench results
        if code != 0:
            logger("Failed to run sysbench test. Check debug log for info.",'c')
            sys.exit(1)
        
        logger("timing: "+str(timing),"i")
        logger("code: "+str(code),"i")

        if mysql_iteration_dynamic_variable: 
            mysql_iteration_dynamic_variable_cur += mysql_iteration_dynamic_variable_inc
            csv_loop(mysql_iteration_dynamic_variable,mysql_iteration_dynamic_variable_cur)
        else:
            csv_loop(False,False)

        loop += 1
        if snmp_disable == False:
            get_snmp_stats()

    if output_graphcode_enable == True:
        graph_handler()

if __name__ == "__main__":
    ''' initialize sysbench output variables '''
    now = str(datetime.datetime.now()).replace(" ",'_')
    sysbench_dict = {}
    dataSetSize = 0
    Qread = False
    Qwrite = False
    Qother = False
    Qtotal = False
    operTx = False
    operTxPerSec = False
    operDeadLocks = False
    operDeadLocksPerSec = False
    operRequests = False
    operRequestsPerSec = False
    operOther = False
    operOtherPerSec = False
    totalTime = False
    totalExecTime = False
    totalExecEvents = False
    execExecSum = False
    execPerRequestMin = False
    execPerRequestMax = False
    execPerRequestAvg = False
    execPerRequest95perc = False
    threadsEventAvg = False
    threadsEventStddev = False
    threadsExecTimeAvg = False
    threadsExecTimeStddev = False
    osLoadMinOne = 0
    osLoadMinFive = 0
    osLoadMinFifteen = 0
    cpuPercUser = 0
    cpuPercSys = 0
    cpuPercIdle = 0
    swapBytesTotal = 0
    swapBytesAvail = 0
    memBytesTotalAvail = 0
    memBytesTotalUsed = 0
    memBytesTotalFree = 0
    memBytesTotalShared = 0
    memBytesTotalBuffered = 0
    memBytesTotalCached = 0
    mysql_iteration_dynamic_variable_cur = False
    mysql_iteration_restart = False
    snmp_disable = False

    ''' start app '''
    header()
    conn = None 
    reuse_conn = True
    (options, args) = parse_options()

    v = helix.versionIdent()
    version = str(v[0]+"."+v[1])
    if version < 2.6: 
        print "Please upgrade to python 2.6+"
        sys.exit(1)

    if options.conf_file:
        sysbench_binary,num_threads,max_requests,max_time,thread_stack_size,init_rng,test,debug,verbosity,percentile,validate,oltp_test_mode,oltp_read_only,oltp_skip_trx,oltp_reconnect_mode,oltp_range_size,oltp_point_selects,oltp_simple_ranges,oltp_sum_ranges,oltp_order_ranges,oltp_distinct_ranges,oltp_index_updates,oltp_non_index_updates,oltp_nontrx_mode,oltp_connect_delay,oltp_user_delay_min,oltp_user_delay_max,oltp_table_name,oltp_table_size,oltp_dist_type,oltp_dist_pct,oltp_dist_res,db_ps_mode,mysql_host,mysql_port,mysql_socket,mysql_user,mysql_password,mysql_db,mysql_table_engine,mysql_ssl,myisam_max_rows,mysql_create_options,snmp_port,snmp_host,snmp_rocommunity,snmp_version,snmp_disable,mysql_iteration_restart,mysql_iteration_dynamic_variable,mysql_iteration_dynamic_variable_min,mysql_iteration_dynamic_variable_inc,mysql_iteration_global_status_watch,mysql_iteration_sql_execute,mysql_iteration_query_cache_flush,iteration_system_command_exec,iteration_sleep,output_file_format,output_oltp_limit_results,output_graphcode_enable,output_graphcode_framework,output_directory,debug_log,print_sql,loops = getconf()

    else:
        sysbench_binary = options.sysbench_binary
        num_threads = int(options.num_threads)
        max_requests = int(options.max_requests)
        max_time = int(options.max_time)
        thread_stack_size = options.thread_stack_size
        init_rng = options.init_rng
        test = options.test
        debug = options.debug
        verbosity = int(options.verbosity)
        percentile = int(options.percentile)
        validate = options.validate
        oltp_test_mode = options.oltp_test_mode
        oltp_read_only = options.oltp_read_only
        oltp_skip_trx = options.oltp_skip_trx
        oltp_reconnect_mode = options.oltp_reconnect_mode
        oltp_range_size = int(options.oltp_range_size)
        oltp_point_selects = int(options.oltp_point_selects)
        oltp_simple_ranges = int(options.oltp_simple_ranges)
        oltp_sum_ranges = int(options.oltp_sum_ranges)
        oltp_order_ranges = int(options.oltp_order_ranges)
        oltp_distinct_ranges = int(options.oltp_distinct_ranges)
        oltp_index_updates = int(options.oltp_index_updates)
        oltp_non_index_updates = int(options.oltp_non_index_updates)
        oltp_nontrx_mode = options.oltp_nontrx_mode
        oltp_connect_delay = options.oltp_connect_delay
        oltp_user_delay_min = int(options.oltp_user_delay_min)
        oltp_user_delay_max = int(options.oltp_user_delay_max)
        oltp_table_name = options.oltp_table_name
        oltp_table_size = options.oltp_table_size
        oltp_dist_type = options.oltp_dist_type
        oltp_dist_pct = int(options.oltp_dist_pct)
        oltp_dist_res = int(options.oltp_dist_res)
        db_ps_mode = options.db_ps_mode
        mysql_host = options.mysql_host
        mysql_port = int(options.mysql_port)
        mysql_socket = options.mysql_socket
        mysql_user = options.mysql_user
        mysql_password = options.mysql_password
        mysql_db = options.mysql_db
        mysql_table_engine = options.mysql_table_engine
        mysql_ssl = options.mysql_ssl
        myisam_max_rows = int(options.myisam_max_rows)
        mysql_create_options = options.mysql_create_options
        snmp_port = int(options.snmp_port)
        snmp_host = options.snmp_host
        snmp_rocommunity = options.snmp_rocommunity
        snmp_version = options.snmp_version
        snmp_disable = options.snmp_disable
        mysql_iteration_restart = options.mysql_iteration_restart
        mysql_iteration_dynamic_variable = options.mysql_iteration_dynamic_variable
        mysql_iteration_dynamic_variable_min = options.mysql_iteration_dynamic_variable_min
        mysql_iteration_dynamic_variable_inc = options.mysql_iteration_dynamic_variable_inc
        mysql_iteration_global_status_watch = options.mysql_iteration_global_status_watch
        mysql_iteration_sql_execute = options.mysql_iteration_sql_execute
        mysql_iteration_query_cache_flush = options.mysql_iteration_query_cache_flush
        iteration_system_command_exec = options.iteration_system_command_exec
        iteration_sleep = options.iteration_sleep
        output_file_format = options.output_file_format
        output_oltp_limit_results = options.output_oltp_limit_results
        output_graphcode_enable = options.output_graphcode_enable
        output_graphcode_framework = options.output_graphcode_framework
        output_directory = options.output_directory
        debug_log = options.debug_log
        print_sql = options.print_sql
        loops = int(options.loops)

    if verbosity == 5:
        debug = True
    if debug == True:
        verbosity = 5

    if mysql_iteration_dynamic_variable_min:
        mysql_iteration_dynamic_variable_min = int(mysql_iteration_dynamic_variable_min)
    if mysql_iteration_dynamic_variable_inc:
        mysql_iteration_dynamic_variable_inc = int(mysql_iteration_dynamic_variable_inc)

    #create log instance
    log = logging.getLogger()
    log.setLevel(logging.DEBUG)
    formatter = logging.Formatter("%(asctime)s - %(levelname)s - %(message)s")

    # debug console output
    c = logging.StreamHandler(sys.stdout)
    if debug == True:
        c.setLevel(logging.DEBUG)
    else:
        if verbosity == 5:
            c.setLevel(logging.DEBUG)
        elif verbosity == 4:
            c.setLevel(logging.INFO)
        elif verbosity == 3:
            c.setLevel(logging.WARN)
        elif verbosity == 2:
            c.setLevel(logging.ERROR)
        elif verbosity == 1:
            c.setLevel(logging.CRITICAL)

    c.setFormatter(formatter)

    # debug logfile
    f = logging.FileHandler(options.debug_log)
    f.setLevel(logging.DEBUG)
    f.setFormatter(formatter)

    log.addHandler(c)
    log.addHandler(f)
    #end log creation


    if print_sql:
        printsql()
    
    logger("---- current settings ----",'i')
    logger("running with python: %s"%(version),'i')
    logger("num_threads: %s"%(num_threads),'i')
    logger("max_requests: %s"%(max_requests),'i')
    logger("max_time: %s"%(max_time),'i')
    logger("thread_stack_size: %s"%(thread_stack_size),'i')
    logger("init_rng: %s"%(init_rng),'i')
    logger("test: %s"%(test),'i')
    logger("debug: %s"%(debug),'i')
    logger("verbosity: %s"%(verbosity),'i')
    logger("percentile: %s"%(percentile),'i')
    logger("validate: %s"%(validate),'i')
    logger("oltp_test_mode: %s"%(oltp_test_mode),'i')
    logger("oltp_read_only: %s"%(oltp_read_only),'i')
    logger("oltp_skip_trx: %s"%(oltp_skip_trx),'i')
    logger("oltp_reconnect_mode: %s"%(oltp_reconnect_mode),'i')
    logger("oltp_range_size: %s"%(oltp_range_size),'i')
    logger("oltp_point_selects: %s"%(oltp_point_selects),'i')
    logger("oltp_simple_ranges: %s"%(oltp_simple_ranges),'i')
    logger("oltp_sum_ranges: %s"%(oltp_sum_ranges),'i')
    logger("oltp_order_ranges: %s"%(oltp_order_ranges),'i')
    logger("oltp_distinct_ranges: %s"%(oltp_distinct_ranges),'i')
    logger("oltp_index_updates: %s"%(oltp_index_updates),'i')
    logger("oltp_non_index_updates: %s"%(oltp_non_index_updates),'i')
    logger("oltp_nontrx_mode: %s"%(oltp_nontrx_mode),'i')
    logger("oltp_connect_delay: %s"%(oltp_connect_delay),'i')
    logger("oltp_user_delay_min: %s"%(oltp_user_delay_min),'i')
    logger("oltp_user_delay_max: %s"%(oltp_user_delay_max),'i')
    logger("oltp_table_name: %s"%(oltp_table_name),'i')
    logger("oltp_table_size: %s"%(oltp_table_size),'i')
    logger("oltp_dist_type: %s"%(oltp_dist_type),'i')
    logger("oltp_dist_pct: %s"%(oltp_dist_pct),'i')
    logger("oltp_dist_res: %s"%(oltp_dist_res),'i')
    logger("db_ps_mode: %s"%(db_ps_mode),'i')
    logger("mysql_host: %s"%(mysql_host),'i')
    logger("mysql_port: %s"%(mysql_port),'i')
    logger("mysql_socket: %s"%(mysql_socket),'i')
    logger("mysql_user: %s"%(mysql_user),'i')
    logger("mysql_password: %s"%(mysql_password),'i')
    logger("mysql_db: %s"%(mysql_db),'i')
    logger("mysql_table_engine: %s"%(mysql_table_engine),'i')
    logger("mysql_ssl: %s"%(mysql_ssl),'i')
    logger("myisam_max_rows: %s"%(myisam_max_rows),'i')
    logger("mysql_create_options: %s"%(mysql_create_options),'i')
    logger("snmp_port: %s"%(snmp_port),'i')
    logger("snmp_host: %s"%(snmp_host),'i')
    logger("snmp_rocommunity: %s"%(snmp_rocommunity),'i')
    logger("snmp_version: %s"%(snmp_version),'i')
    logger("mysql_iteration_restart: %s"%(mysql_iteration_restart),'i')
    logger("mysql_iteration_dynamic_variable: %s"%(mysql_iteration_dynamic_variable),'i')
    logger("mysql_iteration_dynamic_variable_min: %s"%(mysql_iteration_dynamic_variable_min),'i')
    logger("mysql_iteration_dynamic_variable_inc: %s"%(mysql_iteration_dynamic_variable_inc),'i')
    logger("mysql_iteration_global_status_watch: %s"%(mysql_iteration_global_status_watch),'i')
    logger("mysql_iteration_sql_execute: %s"%(mysql_iteration_sql_execute),'i')
    logger("mysql_iteration_query_cache_flush: %s"%(mysql_iteration_query_cache_flush),'i')
    logger("iteration_system_command_exec: %s"%(iteration_system_command_exec),'i')
    logger("iteration_sleep: %s"%(iteration_sleep),'i')
    logger("output_file_format: %s"%(output_file_format),'i')
    logger("output_oltp_limit_results: %s"%(output_oltp_limit_results),'i')
    logger("output_graphcode_enable: %s"%(output_graphcode_enable),'i')
    logger("output_graphcode_framework: %s"%(output_graphcode_framework),'i')
    logger("output_directory: %s"%(output_directory),'i')
    logger("debug_log: %s"%(debug_log),'i')
    logger("loops: %s"%(loops),'i')
    logger("--------------------------",'i')

    f = "%s/.test.qrt"%(output_directory)
    dir_test(output_directory)
    try:            
        file = open(f, 'a')
    except:
        logger("Failed to access report output directory: %s."%(output_directory),"c")
        sys.exit(1)

    db = helix.dbops()
    conn = db.connect(mysql_host,mysql_user,mysql_password,mysql_db)
    if conn == False:
        sys.exit(1)
        
    try:
        retval = main()
    except (KeyboardInterrupt, SystemExit):
        sys.exit(1)



